package com.gl.saasservice.house.service;

import com.gl.common.enums.BacklogItemTypeEnum;
import com.gl.common.enums.HouseAuditStatusEnum;
import com.gl.common.enums.HouseBindStatusEnum;
import com.gl.common.util.SecurityUtils;
import com.gl.common.vo.ApiData;
import com.gl.framework.security.LoginUser;
import com.gl.framework.web.response.Result;
import com.gl.saasservice.entity.*;
import com.gl.saasservice.house.entity.*;
import com.gl.saasservice.house.repository.*;
import com.gl.saasservice.house.vo.CommunityVO;
import com.gl.saasservice.house.vo.HouseVO;
import com.gl.saasservice.repository.*;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.*;
import java.util.stream.Collectors;

@Service
public class HouseService {
    private static final Logger log = LoggerFactory.getLogger(HouseService.class);

    @Autowired
    private DepartmentHierarchyRepository departmentHierarchyRepository;
    @Autowired
    private DepartmentEnterpriseTagRefRepository departmentEnterpriseTagRefRepository;
    @Autowired
    private HouseRepository houseRepository;
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private CommunityBuildRepository communityBuildRepository;
    @Autowired
    private CommunityBuildUnitRepository communityBuildUnitRepository;
    @Autowired
    private CommunityRepository communityRepository;
    @Autowired
    private CohabitRepository cohabitRepository;
    @Autowired
    private BacklogItemRepository backlogItemRepository;
    @Autowired
    private QYUserRepository qYUserRepository;
    @Autowired
    private EstateExpenseBillRepository estateExpenseBillRepository;
    @Autowired
    private EstateRentBillRepository estateRentBillRepository;
    @Autowired
    private DepartmentRepository departmentRepository;
    @Autowired
    private HouseTypeRepository houseTypeRepository;
    @Autowired
    private CommunityHouseUserRepository communityHouseUserRepository;
    public Result pageslist(String token, Integer pageNumber, Integer pageSize, HouseVO vo) {
        log.info("houseService pageslist token={},pageNumber={},pageSize={},vo={}",token,  pageNumber,  pageSize,  vo.toString());

        try {
            StringBuilder where = new StringBuilder();
            List<Object> args = new ArrayList<>();
            where.append(" where 1 = 1");

            where.append("  AND c.status = 1 ");

            //查询条件
            if(vo != null){
                //房屋ID
                if (StringUtils.isNotBlank(vo.getId())) {
                    where.append(" and h.id = ?");
                    args.add(vo.getId());
                }
                //小区id
                if(StringUtils.isNotBlank(vo.getResidenceCommunityId())){
                    where.append(" and h.residence_community_id = ?");
                    args.add(vo.getResidenceCommunityId());
                }
                //楼宇id
                if (vo.getBuildingNum() != null) {
                    where.append(" and h.building_num = ?");
                    args.add(vo.getBuildingNum());
                }
                //单元id
                if (vo.getUnit() != null) {
                    where.append(" and h.unit = ?");
                    args.add(vo.getUnit());
                }
                //房号
                if (StringUtils.isNotBlank(vo.getRoomNumber())) {
                    where.append(" and h.room_number = ?");
                    args.add(vo.getRoomNumber());
                }
                //小区名称
                if(StringUtils.isNotBlank(vo.getResidenceCommunityName())){
                    where.append(" and c.name like ?");
                    args.add("%"+vo.getResidenceCommunityName()+"%");
                }

                //楼层
                if (vo.getFloor() != null) {
                    where.append(" and h.floor = ?");
                    args.add(vo.getFloor());
                }
                //面积(平方米)
                if (vo.getArea() != null) {
                    where.append(" and h.area = ?");
                    args.add(vo.getArea());
                }
                //省
                if (vo.getProvinceId() != null) {
                    where.append(" and h.province_id = ?");
                    args.add(vo.getProvinceId());
                }
                //市
                if (vo.getCityId() != null) {
                    where.append(" and h.city_id = ?");
                    args.add(vo.getCityId());
                }
                //区
                if (vo.getDistrictId() != null) {
                    where.append(" and h.district_id = ?");
                    args.add(vo.getDistrictId());
                }
                //街道
                if (vo.getStreetId() != null) {
                    where.append(" and h.street_id = ?");
                    args.add(vo.getStreetId());
                }
                //社区
                if (vo.getCommunityId() != null) {
                    where.append(" and h.community_id = ?");
                    args.add(vo.getCommunityId());
                }
                //小区地址
                if (StringUtils.isNotBlank(vo.getAddress())) {
                    where.append(" and h.address like ?");
                    args.add("%"+vo.getAddress()+"%");
                }
                //所属物业
                if (vo.getHousePropertyId() != null) {
                    where.append(" and h.house_property_id = ?");
                    args.add(vo.getHousePropertyId());
                }
                //产权所有人、产权单位
                if (StringUtils.isNotBlank(vo.getOwnerName())) {
                    where.append(" and (h.owner_name like ? or d.name like ?)");
                    args.add("%" + vo.getOwnerName() + "%");
                    args.add("%" + vo.getOwnerName() + "%");
                }
                //户型室
                if(vo.getRoom()  != null){
                    where.append(" and h.room = ?");
                    args.add(vo.getRoom());
                }
                //户型厅
                if(vo.getHall() != null){
                    where.append(" and h.hall = ?");
                    args.add(vo.getHall());
                }
                //户型卫
                if(vo.getToilet() != null){
                    where.append(" and h.toilet = ?");
                    args.add(vo.getToilet());
                }
                //性质
                if(vo.getHouseNature() != null){
                    where.append(" and h.house_nature = ?");
                    args.add(vo.getHouseNature());
                }
            }

            String SQL="SELECT h.*,c.name residenceCommunityName,b.name buildingNumName,u.name unitName  " +
                    "   ,a1.name province,a2.name city,a3.name district,a4.name street,a5.name community   "+
                    "   FROM saas_house h  " +
                    "   LEFT JOIN saas_community c ON c.id=h.residence_community_id  " +
                    "   LEFT JOIN saas_community_build b ON b.id=h.building_num  " +
                    "   LEFT JOIN saas_community_build_unit u ON u.id=h.unit  "+
                    "   LEFT JOIN sys_area a1 ON h.province_id=a1.id  " +
                    "   LEFT JOIN sys_area a2 ON h.city_id=a2.id  " +
                    "   LEFT JOIN sys_area a3 ON h.district_id=a3.id  " +
                    "   LEFT JOIN sys_area a4 ON h.street_id=a4.id  " +
                    "   LEFT JOIN sys_area a5 ON h.community_id=a5.id  "+
                    "   LEFT JOIN saas_department d ON h.org_id = d.id";

            String count ="SELECT count(1)  " +
                    "   FROM saas_house h  " +
                    "   LEFT JOIN saas_community c ON c.id=h.residence_community_id  " +
                    "   LEFT JOIN saas_community_build b ON b.id=h.building_num  " +
                    "   LEFT JOIN saas_community_build_unit u ON u.id=h.unit  "+
                    "   LEFT JOIN sys_area a1 ON h.province_id=a1.id  " +
                    "   LEFT JOIN sys_area a2 ON h.city_id=a2.id  " +
                    "   LEFT JOIN sys_area a3 ON h.district_id=a3.id  " +
                    "   LEFT JOIN sys_area a4 ON h.street_id=a4.id  " +
                    "   LEFT JOIN sys_area a5 ON h.community_id=a5.id  "+
                    "   LEFT JOIN saas_department d ON h.org_id = d.id";

            ApiData<HouseVO> apiData = new ApiData<>();

            Long total = jdbcTemplate.queryForObject(count + where, Long.class, args.toArray());
            if (total == 0) {
                return Result.success(apiData);
            }

            // 查询分页数据
            String offsets = " limit ? offset ?";
            where.append(" ORDER BY ").append(" h.create_time desc ").append(" \n").append(offsets);
            args.add(pageSize);
            args.add(pageNumber * pageSize);
            List<HouseVO> data = jdbcTemplate.query(SQL + where,new BeanPropertyRowMapper<>(HouseVO.class), args.toArray());

            List<Long> housePropertyIdList=data.stream().map(HouseVO::getHousePropertyId).collect(Collectors.toList());
            List<DepartmentHierarchy>  departmentHierarchyList=departmentHierarchyRepository.findByOrgIdIn(housePropertyIdList);
            for(HouseVO houseVO : data) {
                for (DepartmentHierarchy departmentHierarchy : departmentHierarchyList) {
                    if (houseVO.getHousePropertyId().equals(departmentHierarchy.getOrgId())) {
                        houseVO.setHouseProperty(departmentHierarchy.getName());
                    }
                }
            }

            // 公租房查询产权单位名称
            List<Long> ownerIds = data.stream().filter(houseVO -> houseVO.getOrgId() != null).map(HouseVO::getOrgId).distinct().collect(Collectors.toList());
            if (!ownerIds.isEmpty()) {
                Map<Long, String> map = departmentRepository.findByIdIn(ownerIds).stream().collect(Collectors.toMap(Department::getId, Department::getName));
                if (!map.isEmpty()) {
                    data.forEach(houseVO -> {
                        if (houseVO.getHouseNature() != null && houseVO.getHouseNature() == 1) {
                            houseVO.setOwnerName(map.get(houseVO.getOrgId()));
                        }
                    });
                }
            }

            apiData.setTotal(total);
            apiData.setData(data);
            return Result.success(apiData);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return Result.fail();
        }

    }

    public Result totallist(String token){
        log.info("houseService   totallist   token={} ",token);

        Map<String,Object> map =new HashMap<>();

        Long communityTotal = jdbcTemplate.queryForObject(" SELECT count(1) FROM saas_community c where c.status = 1  ",Long.class);

        Long houseTotal = jdbcTemplate.queryForObject("SELECT count(1) FROM saas_house  ",Long.class);

        // apply_status '申请状态：0草稿、1未审核 2审批中 3已审核 4审核不通过 6已撤销 9已过期'
        //总户数：公租房资格申请审批通过的户数，以承租人为依据统计  加上非公租房的

        Long houseNum1 = jdbcTemplate.queryForObject(" select COUNT(1) from saas_house_eligible_apply h where apply_status=3  " , Long.class);

        //总户数;非公租房
        Long houseNum2 = jdbcTemplate.queryForObject(" SELECT COUNT(1) as count FROM m_match_user_house \n",Long.class);

        Long houseNum = houseNum1 + houseNum2;

        List<Eligible>  houseNumData= jdbcTemplate.query(" select h.* from saas_house_eligible_apply h where apply_status=3 " , new BeanPropertyRowMapper<>(Eligible.class));

        //总人数：公租房资格申请审批通过的总人数（承租人+同住人） 加上非公租房的承租人和同住人
        List<Long> applyIds=houseNumData.stream().map(Eligible::getId).collect(Collectors.toList());
        List<Cohabit> cohabit= cohabitRepository.findByApplyIdIn(applyIds);

        //公租房的总人口数
        Long peopleNum1=houseNum1+cohabit.size();
        //非公租房的总人口数
        Long peopleNum2 = jdbcTemplate.queryForObject(" SELECT COUNT(1) FROM saas_community_house_user WHERE status =1 and type != 3",Long.class);
        Long peopleNum = peopleNum1 + peopleNum2;

        map.put("communityTotal",communityTotal);  //小区总数
        map.put("houseTotal",houseTotal);    //房屋总数
        map.put("houseNum",houseNum); //户数
        map.put("peopleNum",peopleNum); //总人口数


        return Result.success(map);
    }

    public Result findCommunity(String token) {
        log.info("houseService   findCommunity   token={} ",token);
        try {

            List<Map<String, Object>> list = jdbcTemplate.queryForList("SELECT c.id AS communityId,c.name AS communityName FROM `saas_community` c  where c.status = 1");

            return Result.success(list);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return Result.fail();
        }
    }

    public Result findBuild(String communityId) {
        try {
            if (StringUtils.isBlank(communityId)) {
                return Result.fail("小区id为空");
            }

            List<CommunityBuild> builds = communityBuildRepository.findByCommunityId(communityId);
            List<Map<String, Object>> list = new ArrayList<>();
            for (CommunityBuild build : builds) {
                Map<String, Object> map = new HashMap<>();
                map.put("buildId", build.getId());
                map.put("buildName", build.getName());
                list.add(map);
            }

            return Result.success(list);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return Result.fail();
        }
    }

    public Result findUnit(String communityId, Long buildId) {
        try {
            if (StringUtils.isBlank(communityId)) {
                return Result.fail("小区id为空");
            }
            if (buildId == null) {
                return Result.fail("楼栋id为空");
            }

            List<CommunityBuildUnit> units = communityBuildUnitRepository.findByCommunityIdAndBuildId(communityId, buildId);
            List<Map<String, Object>> list = new ArrayList<>();
            for (CommunityBuildUnit unit : units) {
                Map<String, Object> map = new HashMap<>();
                map.put("unitId", unit.getId());
                map.put("unitName", unit.getName());
                list.add(map);
            }

            return Result.success(list);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return Result.fail();
        }
    }

    public Result findRoomNumber(String communityId, Long buildId, Long unitId) {
        try {
            if (StringUtils.isBlank(communityId)) {
                return Result.fail("小区id为空");
            }
            if (buildId == null) {
                return Result.fail("楼栋id为空");
            }
            if (unitId == null) {
                return Result.fail("单元id为空");
            }

            List<Integer> houseNatureList = new ArrayList<>();
            houseNatureList.add(1);
            houseNatureList.add(2);
            houseNatureList.add(3);
            houseNatureList.add(4);
            houseNatureList.add(5);
            List<House> houseList = houseRepository.findByResidenceCommunityIdAndBuildingNumAndUnitAndHouseNatureIn(communityId, buildId, unitId, houseNatureList);
            List<String> list = new ArrayList<>();
            for (House house : houseList) {
                list.add(house.getRoomNumber());
            }

            return Result.success(list);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return Result.fail();
        }
    }

    public Result findCommunityByArea(String token,Long area){
        log.info("houseService    findCommunityByArea  token={} , area={}  ",token,area);

        List<Community> communityList = communityRepository.findByCommunityIdAndStatus(area,1);

        List<CommunityVO> voList=new ArrayList<>();
        communityList.forEach(community -> {
            CommunityVO vo =new CommunityVO();
            BeanUtils.copyProperties(community,vo);
            voList.add(vo);
        });

        return Result.success(voList);
    }

    public Result findCommunityByCityId(String token,Long cityId){
        log.info("houseService    findCommunityByArea  token={} , area={}  ",token,cityId);

        List<Community> communityList = communityRepository.findByCityIdAndStatus(cityId,1);

        List<CommunityVO> voList=new ArrayList<>();
        communityList.forEach(community -> {
            CommunityVO vo =new CommunityVO();
            BeanUtils.copyProperties(community,vo);
            voList.add(vo);
        });

        return Result.success(voList);
    }

    public Result findByProvinceId(String token,Long provinceId){
        log.info("houseService   findByProvinceId   token ={} , provinceId={} ",token,provinceId);

        List<Long> ids = allChildByArea(provinceId);
        ids.add(provinceId);

        List<DepartmentHierarchy> hierarchyList=departmentHierarchyRepository.findByAreaIdInAndStatus(ids,1);

        //物业公司集合
        List<Map<String,Object>> propertyList=new ArrayList<>();

        for( DepartmentHierarchy dh : hierarchyList){
            List<DepartmentEnterpriseTagRef> entityList = departmentEnterpriseTagRefRepository.findByDepRefId(dh.getId());
            List<Long> tagIdList=entityList.stream().map(DepartmentEnterpriseTagRef::getTagId).collect(Collectors.toList());
            if(tagIdList.contains(4L)){    //物业公司
                Map<String,Object> map=new HashMap<>();
                map.put("orgId",dh.getOrgId());
                map.put("orgName",dh.getName());
                propertyList.add(map);
            }
        }

        return Result.success(propertyList);
    }

    @Transactional(rollbackFor=Exception.class)
    public Result communityProperty(String token,CommunityVO vo){
        log.info("houseService   communityProperty   token ={} , vo={} ",token,vo.toString());

        if(StringUtils.isBlank(vo.getId())){
            return Result.fail("小区id不能为空");
        }
        if(vo.getOrgId() ==null){
            return Result.fail("物业企业id不能为空");
        }
        if(StringUtils.isBlank(vo.getPropertyCompanyName())){
            return Result.fail("物业公司名称不能为空");
        }

        Optional<Community> communityOptional = communityRepository.findById(vo.getId());
        if(!communityOptional.isPresent()){
            return Result.fail("数据不存在");
        }

        Community community = communityOptional.get();

        community.setOrgId(vo.getOrgId());
        community.setPropertyCompanyName(vo.getPropertyCompanyName());

        communityRepository.save(community);

        //同步更新所有绑定了该小区的公租房与物业房产对应的 物业公司数据

        List<House> houseList = houseRepository.findByResidenceCommunityId(vo.getId());

        // `house_property_id` : 所属物业
        houseList.forEach(house -> {
            house.setHousePropertyId(vo.getOrgId());
        });

        houseRepository.saveAll(houseList);

        return Result.success(null);
    }


    @Transactional
    public Result updateBindOwner(String token, String houseId, Long orgId) {

        LoginUser loginUser = SecurityUtils.getLoginUser();
        try {

            if (StringUtils.isBlank(houseId)) {
                return Result.fail("公租房id为空");
            }
            if (orgId == null) {
                return Result.fail("产权所有人id为空");
            }

            Optional<House> houseOpt = houseRepository.findById(houseId);
            if (!houseOpt.isPresent()) {
                return Result.fail("公租房不存在");
            }
            House house = houseOpt.get();
            Date date = new Date();

            Integer auditStatus = house.getAuditStatus();
            if (auditStatus == HouseAuditStatusEnum.NO_AUDIT.getValue()) {
                //删除未审核待办事项
                jdbcTemplate.update("DELETE from saas_backlog_item WHERE house_id = ? AND type = 1 AND handle_status = 0", houseId);
            }

            //绑定审批状态
            Map<String, String> map = findEnterpriseAuditStatus(house.getProvinceId(), house.getCityId(), house.getDistrictId(), house.getStreetId(), house.getCommunityId());
            String status = map.get("status");

            house.setOrgId(orgId);
            house.setBindStatus(HouseBindStatusEnum.BIND.getValue());
            if (status.equals("0")) {//不需要审批
                house.setAuditStatus(HouseAuditStatusEnum.AUDIT.getValue());
            } else {//需要审批
                house.setAuditStatus(HouseAuditStatusEnum.NO_AUDIT.getValue());
                //生成代办事项
                BacklogItem backlogItem = new BacklogItem();
                backlogItem.setHouseId(house.getId());
                String auditOrgId = map.get("orgId");
                backlogItem.setOrgId(Long.parseLong(auditOrgId));
                //backlogItem.setUserOrgId(enterpriseId);
                backlogItem.setType(BacklogItemTypeEnum.HOUSE_SAVE.getValue());
                backlogItem.setTitle( loginUser.getUser().getUserName()+ "提交的绑定公租房申请");//department.getName()
                backlogItem.setBusinessStatus("待审批");
                backlogItem.setHandleStatus(0);//0未读/未处理
                backlogItem.setOperatorId(loginUser.getUser().getId().toString());//userId
                backlogItem.setCreateTime(date);
                backlogItemRepository.save(backlogItem);
            }
            houseRepository.save(house);

            return Result.success(null);
        } catch (Exception e) {
            log.error("HouseService updateBindOwner Exception = {}" + e.getMessage(), e);
            return Result.fail();
        }
    }

    /**
     * 公租房绑定产权单位审批验证
     *
     * @param provinceId  省
     * @param cityId      市
     * @param districtId  区/县
     * @param streetId    街道
     * @param communityId 社区
     * @return
     * @author shun
     * @date 2021年6月30日
     */
    public Map<String, String> findEnterpriseAuditStatus(Long provinceId, Long cityId, Long districtId, Long streetId,
                                                         Long communityId) {
        String sql = "SELECT h.org_id as orgId,h.area_id AS areaId,h.bind_auit_status AS bindAuitStatus "
                + "FROM saas_department_hierarchy h "
                + "LEFT JOIN saas_department d ON d.id = h.org_id "
                + "LEFT JOIN saas_department_enterprise_tag_ref f ON h.id = f.dep_ref_id "
                + "WHERE f.tag_id = 5 ";

        StringBuffer buf = new StringBuffer();
        if (provinceId != null) {
            buf.append(" AND (d.province_id=").append(provinceId);
        }

        if (cityId != null) {
            if (StringUtils.isEmpty(buf)) {
                buf.append(" AND (d.city_id=").append(cityId);
            } else {
                buf.append(" or d.city_id=").append(cityId);
            }
        }

        if (districtId != null) {
            if (StringUtils.isEmpty(buf)) {
                buf.append(" AND (d.district_id=").append(districtId);
            } else {
                buf.append(" or d.district_id=").append(districtId);
            }
        }

        if (streetId != null) {
            if (StringUtils.isEmpty(buf)) {
                buf.append(" AND (d.street_id=").append(streetId);
            } else {
                buf.append(" or d.street_id=").append(streetId);
            }
        }

        if (communityId != null) {
            if (StringUtils.isEmpty(buf)) {
                buf.append(" AND (d.community_id=").append(communityId);
            } else {
                buf.append(" or d.community_id=").append(communityId);
            }
        }

        if (StringUtils.isNotEmpty(buf)) {
            buf.append(")");
        }

        Map<String, String> map = new HashMap<>();
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql + buf.toString());
        if (list.isEmpty()) {
            // 结果为空，默认不需要审批
            map.put("code", "10000");
            map.put("status", "0");// 不需要审批
            map.put("orgId", null);// 审批企业ID
            return map;
        }

        // 查询公租房绑定产权单位审批人
        // 匹配社区住建局
        Long orgId = getAreaAuditStatus(list, communityId);
        // 匹配街道住建局
        if (orgId == null) {
            orgId = getAreaAuditStatus(list, streetId);
        }
        // 匹配区/县住建局
        if (orgId == null) {
            orgId = getAreaAuditStatus(list, districtId);
        }
        // 匹配市住建局
        if (orgId == null) {
            orgId = getAreaAuditStatus(list, cityId);
        }
        // 匹配省住建局
        if (orgId == null) {
            orgId = getAreaAuditStatus(list, provinceId);
        }

        if (orgId == null) {
            log.info("获取审批人信息失败, 未找到需要审批的住建局, orgId={}", orgId);
            map.put("code", "10000");
            map.put("status", "0");// 不需要审批
            map.put("orgId", null);// 审批人为节点审批人
            return map;
        }

        map.put("code", "10000");
        map.put("status", "1");// 需要审批
        map.put("orgId", String.valueOf(orgId));// 审批企业ID
        return map;
    }

    /**
     * 获取需要审批的住建局企业id
     *
     * @param list
     * @param areaId
     * @return
     * @author shun
     * @date 2021年6月30日
     */
    public Long getAreaAuditStatus(List<Map<String, Object>> list, Long areaId) {
        for (Map<String, Object> mp : list) {
            // 企业区域id
            Long id = Long.parseLong(mp.get("areaId").toString());
            if (areaId.longValue() == id.longValue() && mp.get("bindAuitStatus") != null) {
                Integer status = Integer.parseInt(mp.get("bindAuitStatus").toString());
                // 0不需要审批 1需要审批
                if (status == 1) {
                    Long orgId = Long.parseLong(mp.get("orgId").toString());
                    return orgId;
                }
            }
        }

        return null;
    }

    public Result updatePropertyOwner(String token,String ownerName,String ownerMobile,String houseId){
        log.info(" houseService  updatePropertyOwner   token={},ownerName={} , ownerMobile={},  houseId={} ", token,ownerName ,ownerMobile, houseId );

        if(StringUtils.isBlank(ownerMobile)){
            return Result.fail("产权所有人电话为空");
        }
        if(StringUtils.isBlank(ownerName)){
            return Result.fail("产权所有人姓名为空");
        }
        if(StringUtils.isBlank(houseId)){
            return Result.fail("房产id为空");
        }

        Optional<House> houseOpt = houseRepository.findById(houseId);
        if (!houseOpt.isPresent()) {
            return Result.fail("房产不存在");
        }

        House house= houseOpt.get();

        //产权所有人id ：根据电话号码匹配用户
        User user = qYUserRepository.findByMobile(ownerMobile);
        if(user != null){
            house.setOwnerUserId(user.getId());
        }

        house.setOwnerName(ownerName);
        house.setOwnerMobile(ownerMobile);

        houseRepository.save(house);

        return Result.success(null);
    }

    public Result ownerList(String token, String provinceId) {
        try {

            String sql = "SELECT h.`org_id` AS orgId,h.`name` AS orgName FROM `saas_department_hierarchy` h LEFT JOIN `saas_department_enterprise_tag_ref` r ON h.id = r.dep_ref_id  \n" +
                    "WHERE h.`org_id` IN (SELECT d.id FROM `saas_department` d WHERE d.`province_id` = ? AND d.`parent_id` = 0) \n" +
                    "AND r.`tag_id` = 2";


            List<Object> args = new ArrayList<>();
            args.add(provinceId);

            List<Map<String,Object>> list = jdbcTemplate.queryForList(sql , args.toArray());

            return Result.success(list);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return Result.fail();
        }
    }

    public Result delete(String token,String id,Integer type){

        if(StringUtils.isBlank(id)){
            return Result.fail("id为空");
        }

        Optional<House> houseOptional = houseRepository.findById(id);
        if(!houseOptional.isPresent()){
            return Result.fail("数据不存在");
        }

        House house=houseOptional.get();

        //type 房屋类型：1 公租房 2 物业房产
        if(type.intValue() == 1){
            //公租房：已配租的不能删除，出租状态为：已过期可以删除
            if(house.getMatchStatus().intValue() == 1 || house.getMatchStatus().intValue() == 2
                    || house.getLeaseStatus().intValue() == 0 || house.getLeaseStatus().intValue() == 1){
                return Result.fail("可配租/已配租/未出租/已出租的不能删除");
            }

            houseRepository.deleteById(id);
        }

        if(type.intValue() == 2){
            //物业房产：有账单的不能删除
            List<EstateExpenseBill> estateExpenseBill = estateExpenseBillRepository.findByHouseId( id);
            List<EstateRentBill> estateRentBill = estateRentBillRepository.findByHouseId( id);
            if(estateExpenseBill.size()>0 || estateRentBill.size()>0){
                return Result.fail("有账单,不能删除");
            }

            houseRepository.deleteById(id);
        }

        return Result.success(null);
    }

    //查找当前机构层级节点所有节点(不包含当前节点)sys_area
    public List<Long> allChildByArea(Long id) {
        String sql = " select id from ( select t1.id,if(find_in_set(parent_id, @pids) > 0,@pids := concat(@pids, ',', id),0) as ischild from ( select id,parent_id from `sys_area` t order by parent_id, id ) t1,(select @pids := ?) t2 ) t3 where ischild != 0 \n";
        return jdbcTemplate.queryForList(sql, new Object[]{id}, Long.class);
    }

    /**
     * 新增、修改房屋类型
     * @param token
     * @param houseType 房屋类型
     * @return
     */
    public Result houseType(String token, HouseType houseType) {
        if (houseType.getType() == null) {
            return Result.fail("房屋类型为空");
        }
        if (houseType.getId() != null) {
            Optional<HouseType> byId = houseTypeRepository.findById(houseType.getId());
            HouseType byType = byId.get();
            byType.setType(houseType.getType());
            houseTypeRepository.save(byType);
            return Result.success("修改成功！");
        } else {
            HouseType type = new HouseType();
            type.setType(houseType.getType());
            houseTypeRepository.save(type);
            return Result.success("新增成功！");
        }
    }

    /**
     * 查询房屋类型
     * @param token
     * @return
     */
    public Result findHouseType(String token) {
        Iterable<HouseType> all = houseTypeRepository.findAll();
        return Result.success(all);
    }

    /**
     *   删除房屋类型
     *
     */
    public Result deleteHouseType(String token, Long id) {
        if (id == null) {
            return Result.fail("请选择要删除的类型");
        }
        if (id < 8) {
            return Result.fail("默认房屋类型不可删除");
        }
        Optional<HouseType> byId = houseTypeRepository.findById(id);
        if (byId.isPresent()) {
            houseTypeRepository.deleteById(id);
            return Result.success("删除成功");
        }else {
            return Result.fail("房屋类型不存在");
        }

    }

    /**
     *   删除小区
     *
     */
    public Result deleteCommunity(String token, String id) {
        if (id == null) {
            return Result.fail("请选择要删除的小区");
        }
        List<House> houseList = houseRepository.findByResidenceCommunityIdAndStatus(id, 1);
        if (!houseList.isEmpty()) {
            for (House house : houseList) {
                String houseId = house.getId();
                Optional<House> optional = houseRepository.findById(houseId);
                if (optional.isPresent()) {
                    //有承租人的房产不能删除
                    List<CommunityHouseUser> byCommunityHouseIdAndStatus = communityHouseUserRepository.findByCommunityHouseIdAndStatus(houseId, 1);
                    if (!byCommunityHouseIdAndStatus.isEmpty()) {
                        return  Result.fail("房产有承租人不能删除");
                    }
                    //物业房产：有账单的不能删除
                    List<EstateExpenseBill> estateExpenseBill = estateExpenseBillRepository.findByHouseId(houseId);
                    List<EstateRentBill> estateRentBill = estateRentBillRepository.findByHouseId(houseId);
                    if (!estateExpenseBill.isEmpty() || !estateRentBill.isEmpty()) {
                        return  Result.fail("房产有账单不能删除");
                    }
                    houseRepository.deleteById(houseId);
                    //删除绑定待办事项
                    jdbcTemplate.update("DELETE from saas_backlog_item WHERE house_id = ? AND type = 1", houseId);
                }
            }
        }
        //删除小区
        communityRepository.deleteById(id);
        return Result.success("删除成功");
    }
}
